﻿/* FUNCTIONS */


CREATE TYPE tipoCidade AS (  
	idcidade integer,
        cidade character varying(50)); 

CREATE OR REPLACE FUNCTION func_cidade_estado(num_estado int) RETURNS SETOF tipoCidade AS $func_cidade_estado$
	DECLARE
		row_cidade tipoCidade%ROWTYPE;
	BEGIN
		FOR row_cidade IN SELECT idcidade, nome FROM cidade WHERE fkidestado = num_estado LOOP
			RETURN NEXT row_cidade;
		END LOOP;
		RETURN;
	END;	 
$func_cidade_estado$ LANGUAGE PLPGSQL

-- Formatar CPF com vetor
CREATE OR REPLACE FUNCTION CPF_Validar_Sem_Loop(par_cpf varchar(11)) RETURNS integer AS $$
DECLARE
vet_cpf integer [11]; --Recebe o CPF DIGITADO
dv integer; -- Primeiro número do DV (Dígito Verificador)
dv1 integer; -- Segundo número do DV (Dígito Verificador)
soma integer; -- Soma utilizada para o cálculo do DV
rest integer; -- Resto da divisão
BEGIN
IF char_length(par_cpf) = 11 THEN
ELSE
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 0;
END IF;
-- Atribuição dos valores do Vetor
vet_cpf[0] := cast(substring (par_cpf FROM 1 FOR 1) as integer);
vet_cpf[1] := cast(substring (par_cpf FROM 2 FOR 1) as integer);
vet_cpf[2] := cast(substring (par_cpf FROM 3 FOR 1) as integer);
vet_cpf[3] := cast(substring (par_cpf FROM 4 FOR 1) as integer);
vet_cpf[4] := cast(substring (par_cpf FROM 5 FOR 1) as integer);
vet_cpf[5] := cast(substring (par_cpf FROM 6 FOR 1) as integer);
vet_cpf[6] := cast(substring (par_cpf FROM 7 FOR 1) as integer);
vet_cpf[7] := cast(substring (par_cpf FROM 8 FOR 1) as integer);
vet_cpf[8] := cast(substring (par_cpf FROM 9 FOR 1) as integer);
vet_cpf[9] := cast(substring (par_cpf FROM 10 FOR 1) as integer);
vet_cpf[10] := cast(substring (par_cpf FROM 11 FOR 1) as integer);
-- CÁLCULO DO PRIMEIRO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 10, 9, 8 e assim por diante...
soma:=(vet_cpf[0]*10)+(vet_cpf[1]*9)+(vet_cpf[2]*8)+(vet_cpf[3]*7)+(vet_cpf[4]*6)+(vet_cpf[5]*5)+(vet_cpf[6]*4)+(vet_cpf[7]*3)+(vet_cpf[8]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv:=0;
ELSE dv:=(11-rest); END IF;
-- CÁLCULO DO SEGUNDO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 11, 10, 9 e assim por diante...
soma:=(vet_cpf[0]*11)+(vet_cpf[1]*10)+(vet_cpf[2]*9)+(vet_cpf[3]*8)+(vet_cpf[4]*7)+(vet_cpf[5]*6)+(vet_cpf[6]*5)+(vet_cpf[7]*4)+(vet_cpf[8]*3)+(dv*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
dv1:=0;
ELSE dv1:=(11-rest); END IF;
IF (dv = vet_cpf[9]) and (dv1 = vet_cpf[10]) THEN
RETURN 1;
ELSE
RAISE NOTICE 'DV do CPF Inválido: %',$1;
RETURN 0;
END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION verify_cnpj(text)
RETURNS BOOLEAN AS $$
-- se o tamanho for 14 prossiga com o cálculo
-- senão retorne falso
SELECT CASE WHEN LENGTH($1) = 14 THEN
(
  -- verifica se os dígitos coincidem com os especificados
  SELECT
      SUBSTR($1, 13, 1) = CAST(digit1 AS text) AND
      SUBSTR($1, 14, 1) = CAST(digit2 AS text)
  FROM
  (
    -- calcula o segundo dígito verificador (digit2)
    SELECT
        -- se o resultado do módulo for 0 ou 1 temos 0
        -- senão temos a subtração de 11 pelo resultado do módulo
        CASE res2
        WHEN 0 THEN 0
        WHEN 1 THEN 0
        ELSE 11 - res2
        END AS digit2,
        digit1
    FROM
    (
      -- soma da multiplicação dos primeiros 9 dígitos por 11, 10, ..., 4, 3
      -- obtemos o módulo da soma por 11
      SELECT MOD(SUM(res2) + digit1 * 2, 11) AS res2,
          digit1
      FROM
      (
        SELECT
            SUM(m * CAST(SUBSTR($1, 7 - m, 1) AS INTEGER)) AS res2
        FROM
        (
          SELECT generate_series(6, 2, -1) AS m
        ) AS m11
        UNION ALL
        SELECT
            SUM(m * CAST(SUBSTR($1, 15 - m, 1) AS INTEGER)) AS res2
        FROM
        (
          SELECT generate_series(9, 3, -1) AS m
        ) AS m12
      ) AS m2,
      (
        -- calcula o primeiro dígito verificador (digit1)
        SELECT
            -- se o resultado do módulo for 0 ou 1 temos 0
            -- senão temos a subtração de 11 pelo resultado do módulo
            CASE res1
            WHEN 0 THEN 0
            WHEN 1 THEN 0
            ELSE 11 - res1
            END AS digit1
        FROM
        (
          -- soma da multiplicação dos primeiros 12 dígitos por 5, 4, 3, 2, 9, 8, 7, ..., 3, 2
          -- obtemos o módulo da soma por 11
          SELECT MOD(SUM(res1), 11) AS res1
          FROM
          (
            SELECT
                SUM(n * CAST(SUBSTR($1, 6 - n, 1) AS INTEGER)) AS res1
            FROM
            (
              SELECT generate_series(5, 2, -1) AS n
            ) AS m11
            UNION ALL
            SELECT
                SUM(n * CAST(SUBSTR($1, 14 - n, 1) AS INTEGER)) AS res1
            FROM
            (
              SELECT generate_series(9, 2, -1) AS n
            ) AS m12
          ) AS m1
        ) AS sum1
      ) AS first_digit
      GROUP BY digit1
    ) AS sum2
  ) AS first_sec_digit
)
ELSE FALSE END;
 
$$ LANGUAGE 'SQL'
IMMUTABLE STRICT;
 
COMMENT ON FUNCTION verify_cnpj(text) IS 'retorna verdadeiro se e, somente se, o CNPJ 
possui o tamanho correto (14 dígitos) e os dígitos calculados coincidem com os especificados';

SELECT * FROM CIDADE WHERE FKIDESTADO = 16

CREATE OR REPLACE FUNCTION func_estado_pais(num_pais int) RETURNS SETOF estado AS $func_estado_pais$
	DECLARE
		row_estado estado%ROWTYPE;
	BEGIN
		FOR row_estado IN SELECT * FROM estado WHERE fkidpais = num_pais LOOP
			RETURN NEXT row_estado;
		END LOOP;
		RETURN;
	END;	 
$func_estado_pais$ LANGUAGE PLPGSQL

SELECT * FROM empresas
SELECT * FROM func_estado_pais(1)


CREATE TYPE tipoContatos AS (  
	idcontatos integer,
        contato character varying(100));    

CREATE OR REPLACE FUNCTION func_contato_empresa(n_emp int) RETURNS SETOF tipoContatos AS $func_contato_empresa$
	DECLARE 
		row_contato tipoContatos%ROWTYPE;
	BEGIN
		FOR row_contato IN SELECT idcontatos, nome FROM contatos WHERE fkidempresas = n_emp LOOP
			RETURN NEXT row_contato;
		END LOOP;
		RETURN;
	END;
$func_contato_empresa$ LANGUAGE PLPGSQL

select * from func_contato_empresa(2)

/* Busca avancada empresa */
drop type tipoEmpresas
CREATE TYPE tipoEmpresas AS (  
	idoportunidades integer,
    nome_emp character varying(100),
    nome_produto character varying(100),
    contato character varying(100),
    nome_fase character varying(100),
    FkIdstatus integer);

drop FUNCTION func_busca_empresas(nome varchar(50))
CREATE OR REPLACE FUNCTION func_busca_empresas(nome text) RETURNS SETOF tipoEmpresas AS $func_busca_empresas$
	DECLARE 
		inf_emp tipoEmpresas%ROWTYPE;
	
	BEGIN
		FOR inf_emp IN select idoportunidades, emp.nome, p.nome, c.nome, f.nome, st.status from oportunidades as o 
				inner join empresas as emp
				on o.fkidempresas = emp.idempresas 
				inner join produtos as p
				on o.fkidprodutos = p.idprodutos 
				inner join contatos as c
				on o.fkidcontatos = c.idcontatos 
				inner join fases as f
				on o.fkidfases = f.idfases 
				inner join status as st 
				on o.fkidstatus = st.idstatus 
				where emp.nome ilike 'nome%' 
				and o.fkidstatus = 0 
				or emp.nome ilike 'nome%' 
				and o.fkidstatus = 3 order by emp.nome LOOP 
					RETURN NEXT inf_emp;
				END LOOP;
		RETURN;		
	END;
$func_busca_empresas$ LANGUAGE PLPGSQL

SELECT * from func_busca_empresas('Am')

select * from empresas




select idoportunidades, emp.nome as empresa, p.nome as produto, c.nome as contato, f.nome as fase, st.status
                               from oportunidades as o
                               inner join empresas as emp
                               on o.fkidempresas = emp.idempresas
                               inner join produtos as p
                               on o.fkidprodutos = p.idprodutos
                               inner join contatos as c
                               on o.fkidcontatos = c.idcontatos
                               inner join fases as f
                               on o.fkidfases = f.idfases
                               inner join status as st
                               on o.fkidstatus = st.idstatus
                               where emp.nome ilike 'a%'
                               and st.status = 'ativo'
